"""5.1 Catalogue produit

Revision ID: 7307a3b2cde4
Revises: 69e690478b6c
Create Date: 2019-09-17 17:42:16.477307

"""

# revision identifiers, used by Alembic.
revision = "7307a3b2cde4"
down_revision = "69e690478b6c"

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql


def update_database_structure():
    op.add_column(
        "base_price_study_product",
        sa.Column(
            "general_overhead",
            sa.Numeric(precision=6, scale=5, asdecimal=False),
            nullable=True,
        ),
    )
    op.add_column(
        "base_price_study_product",
        sa.Column(
            "margin_rate",
            sa.Numeric(precision=6, scale=5, asdecimal=False),
            nullable=True,
        ),
    )
    op.execute(
        """update base_price_study_product as b
               inner join price_study_product as p on p.id=b.id
               set b.margin_rate=p.margin_rate, b.general_overhead=p.general_overhead"""
    )
    op.drop_column("price_study_product", "margin_rate")
    op.drop_column("price_study_product", "general_overhead")
    # ### end Alembic commands ###


def migrate_datas():
    from caerp_base.models.base import DBSESSION

    session = DBSESSION()
    from alembic.context import get_bind

    conn = get_bind()
    from zope.sqlalchemy import mark_changed

    mark_changed(session)


def upgrade():
    update_database_structure()
    migrate_datas()


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column(
        "workshop_action",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "userdatas_socialdocs",
        "status",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.add_column(
        "user_datas", sa.Column("parcours_last_avenant", sa.DATE(), nullable=True)
    )
    op.add_column(
        "user_datas", sa.Column("parcours_start_date", sa.DATE(), nullable=True)
    )
    op.add_column(
        "user_datas",
        sa.Column("parcours_contract_type", mysql.VARCHAR(length=4), nullable=True),
    )
    op.add_column(
        "user_datas", sa.Column("parcours_taux_horaire", mysql.FLOAT(), nullable=True)
    )
    op.add_column("user_datas", sa.Column("sortie_date", sa.DATE(), nullable=True))
    op.add_column(
        "user_datas",
        sa.Column(
            "parcours_employee_quality_id",
            mysql.INTEGER(display_width=11),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "user_datas",
        sa.Column("parcours_salary_letters", mysql.VARCHAR(length=100), nullable=True),
    )
    op.add_column(
        "user_datas", sa.Column("parcours_num_hours", mysql.FLOAT(), nullable=True)
    )
    op.add_column(
        "user_datas", sa.Column("parcours_end_date", sa.DATE(), nullable=True)
    )
    op.add_column(
        "user_datas",
        sa.Column(
            "parcours_taux_horaire_letters", mysql.VARCHAR(length=250), nullable=True
        ),
    )
    op.add_column(
        "user_datas", sa.Column("parcours_salary", mysql.FLOAT(), nullable=True)
    )
    op.create_index(
        "fk_user_datas_parcours_employee_quality_id",
        "user_datas",
        ["parcours_employee_quality_id"],
        unique=False,
    )
    op.alter_column(
        "tva", "name", existing_type=mysql.VARCHAR(length=15), nullable=True
    )
    op.alter_column(
        "tva",
        "default",
        existing_type=sa.Boolean(),
        type_=mysql.INTEGER(display_width=11),
        existing_nullable=True,
    )
    op.alter_column(
        "tva",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "trainer_datas",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "third_party",
        "archived",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "templates",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.drop_constraint(
        op.f("fk_task_status_status_person_id"), "task_status", type_="foreignkey"
    )
    op.create_index(
        "fk_task_status_statusPerson_accounts",
        "task_status",
        ["status_person_id"],
        unique=False,
    )
    op.alter_column(
        "task_status",
        "status_person_id",
        existing_type=mysql.INTEGER(display_width=11),
        nullable=False,
    )
    op.alter_column(
        "task_status", "status_comment", existing_type=mysql.TEXT(), nullable=False
    )
    op.alter_column(
        "task_status",
        "status_code",
        existing_type=mysql.VARCHAR(length=10),
        nullable=False,
    )
    op.add_column(
        "task",
        sa.Column(
            "version",
            mysql.INTEGER(display_width=11),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.drop_constraint(op.f("fk_task_status_person_id"), "task", type_="foreignkey")
    op.create_index(
        "fk_task_statusPerson_accounts", "task", ["status_person_id"], unique=False
    )
    op.alter_column(
        "task",
        "status_person_id",
        existing_type=mysql.INTEGER(display_width=11),
        nullable=False,
    )
    op.alter_column(
        "task", "status_comment", existing_type=mysql.TEXT(), nullable=False
    )
    op.alter_column(
        "task", "status", existing_type=mysql.VARCHAR(length=10), nullable=False
    )
    op.alter_column(
        "task",
        "round_floor",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "task",
        "legacy_number",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=False,
    )
    op.alter_column(
        "supplier_payment",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "supplier_invoice",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "statistic_sheet",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "sale_product_training",
        "modality_two",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "sale_product_training",
        "modality_one",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "sale_file_requirement",
        "validation",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "sale_catalog_work_item",
        "locked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "project_type",
        "include_price_study",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "project_type",
        "default",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "project",
        "archived",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "product",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "price_study_work_item",
        "locked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "price_study_work",
        "display_details",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.add_column(
        "price_study_product",
        sa.Column(
            "general_overhead", mysql.DECIMAL(precision=6, scale=5), nullable=True
        ),
    )
    op.add_column(
        "price_study_product",
        sa.Column("margin_rate", mysql.DECIMAL(precision=6, scale=5), nullable=True),
    )
    op.alter_column(
        "payment_conditions",
        "default",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "payment",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "payment",
        "bank_remittance_id",
        existing_type=mysql.VARCHAR(length=255),
        nullable=False,
    )
    op.alter_column(
        "oidc_token",
        "revoked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "oidc_id_token",
        "revoked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "oidc_code",
        "revoked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "oidc_client",
        "revoked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "login",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "invoice",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "indicator",
        "forced",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "groups",
        "primary",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "groups", "label", existing_type=mysql.VARCHAR(length=255), nullable=True
    )
    op.alter_column(
        "groups",
        "editable",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "external_activity_datas",
        "employer_visited",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expensetel_type",
        "percentage",
        existing_type=mysql.INTEGER(display_width=11),
        nullable=True,
    )
    op.alter_column(
        "expensetel_type",
        "initialize",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expensekm_type",
        "amount",
        existing_type=sa.Float(precision=4),
        type_=mysql.FLOAT(),
        nullable=True,
    )
    op.alter_column(
        "expense_type", "label", existing_type=mysql.VARCHAR(length=50), nullable=True
    )
    op.alter_column(
        "expense_type",
        "contribution",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_type", "code", existing_type=mysql.VARCHAR(length=15), nullable=True
    )
    op.alter_column(
        "expense_type",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_sheet",
        "purchase_exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_sheet",
        "justified",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_sheet",
        "expense_exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_payment",
        "waiver",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "expense_payment",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "estimation",
        "geninv",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "custom_invoice_book_entry_module",
        "enabled",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "custom_invoice_book_entry_module",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "configurable_option",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column("config", "value", existing_type=mysql.TEXT(), nullable=False)
    op.alter_column("company", "updated_at", existing_type=sa.DATE(), nullable=True)
    op.alter_column(
        "company",
        "internal",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=False,
    )
    op.alter_column("company", "created_at", existing_type=sa.DATE(), nullable=True)
    op.alter_column(
        "company",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "career_stage",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "cancelinvoice",
        "exported",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "cae_situation_option",
        "is_integration",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "business_type_task_mention",
        "mandatory",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "business_type_file_type",
        "validation",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "business_type",
        "bpf_related",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=False,
    )
    op.alter_column(
        "business_payment_deadline",
        "invoiced",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "business_payment_deadline",
        "deposit",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "business_bpf_data",
        "is_subcontract",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=False,
    )
    op.alter_column(
        "business",
        "closed",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.drop_constraint(
        op.f("fk_baseexpense_line_customer_id"), "baseexpense_line", type_="foreignkey"
    )
    op.drop_constraint(
        op.f("fk_baseexpense_line_project_id"), "baseexpense_line", type_="foreignkey"
    )
    op.drop_constraint(
        op.f("fk_baseexpense_line_business_id"), "baseexpense_line", type_="foreignkey"
    )
    op.create_foreign_key(
        "fk_baseexpense_line_customer_id",
        "baseexpense_line",
        "customer",
        ["customer_id"],
        ["id"],
    )
    op.create_foreign_key(
        "fk_baseexpense_line_project_id",
        "baseexpense_line",
        "project",
        ["project_id"],
        ["id"],
    )
    op.create_foreign_key(
        "fk_baseexpense_line_business_id",
        "baseexpense_line",
        "business",
        ["business_id"],
        ["id"],
    )
    op.alter_column(
        "baseexpense_line",
        "valid",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_project_type",
        "private",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_project_type",
        "editable",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_project_type",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_price_study_product",
        "locked",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.drop_column("base_price_study_product", "margin_rate")
    op.drop_column("base_price_study_product", "general_overhead")
    op.alter_column(
        "base_accounting_measure_type_category",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_accounting_measure_type",
        "is_total",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_accounting_measure_type",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "base_accounting_measure",
        "value",
        existing_type=sa.Float(precision=2),
        type_=mysql.FLOAT(),
        existing_nullable=True,
    )
    op.alter_column(
        "bank_account",
        "default",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "activity_type",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "activity_action",
        "active",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=True,
    )
    op.alter_column(
        "accounts",
        "vehicle",
        existing_type=sa.String(length=66),
        type_=mysql.VARCHAR(length=126),
        existing_nullable=True,
    )
    op.alter_column(
        "accounts",
        "special",
        existing_type=sa.Boolean(),
        type_=mysql.TINYINT(display_width=1),
        existing_nullable=False,
    )
    op.alter_column(
        "accounts", "civilite", existing_type=mysql.VARCHAR(length=10), nullable=True
    )
    op.alter_column(
        "accounting_operation",
        "debit",
        existing_type=sa.Float(precision=2),
        type_=mysql.FLOAT(),
        existing_nullable=True,
    )
    op.alter_column(
        "accounting_operation",
        "credit",
        existing_type=sa.Float(precision=2),
        type_=mysql.FLOAT(),
        existing_nullable=True,
    )
    op.alter_column(
        "accounting_operation",
        "balance",
        existing_type=sa.Float(precision=2),
        type_=mysql.FLOAT(),
        existing_nullable=True,
    )
    # ### end Alembic commands ###
